package com.css.fxfzypg.common.utils.excel;


import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;

import javax.annotation.Resource;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ Description: 读取excel工具类
 */
@Slf4j
public class ExcelReaderUtil {
    /**
     * Excel读取 操作,返回内容
     */

    public static List<List<String>> readExcel(InputStream is)
            throws IOException {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 循环Excel的行 */
        for (int r = 2; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
//                    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
//                    cellValue = hSSFDataFormatter.formatCellValue(cell);
                    cellValue = parseExcel(cell, wb);
                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    private static String parseExcel(Cell cell, Workbook workbook) {
        String result = new String();
//        CellType cellType = cell.getCellType();
        switch (cell.getCellType()) {
            case NUMERIC:// 数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm:ss")) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
//                    CellStyle style = cell.getCellStyle();
//                    DecimalFormat format = new DecimalFormat();
//                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
//                    if (temp.equals("General")) {
//                        format.applyPattern("#");
//                        result = format.format(value);
//                    } else {
                    result = String.valueOf(value);
//                    }
                }
                break;
            case STRING:// String类型
//                result = cell.getRichStringCellValue().toString();
//                if (StringUtils.isNumeric(cell.getRichStringCellValue().toString())) {
//                    double value = cell.getNumericCellValue();
//                    CellStyle style = cell.getCellStyle();
//                    DecimalFormat format = new DecimalFormat();
//                    String temp = style.getDataFormatString();
//                    // 单元格设置成常规
//                    if (temp.equals("General")) {
//                        format.applyPattern("#");
//                    }
//                    result = format.format(value);
//                } else {
                result = cell.getRichStringCellValue().toString();
//                }
                break;
            case FORMULA:
//                String formula = cell.getCellFormula();
//                System.out.println("公式：" + formula);

                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue.getCellType() == CellType.NUMERIC) {
                    // 处理经度问题
                    NumberFormat numberFormat = NumberFormat.getInstance();
                    numberFormat.setGroupingUsed(false);
                    numberFormat.setMaximumFractionDigits(10);
                    return numberFormat.format(Double.valueOf(cellValue.getNumberValue()));
                }
                break;
            case BLANK:
                result = "";
            default:
                result = "";
                break;
        }
        return result;
    }

    public static List<List<String>> readExcel2(InputStream is)
            throws IOException {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    cellValue = hSSFDataFormatter.formatCellValue(cell);
                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    /**
     * @methodName: readExcel 读取excel工具类
     * @param: [is, tClass]  传入的实体类,成员变量类型只能是基本类型和字符串
     * @return: java.util.List<T>
     * @Description: 读取excel文件, 将其转换为javabean
     */
    public static <T> ExcelResult readExcelBean(InputStream is, Class<T> tClass)
            throws IOException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        List<List<String>> list = ExcelReaderUtil.readExcel2(is);
        //-----------------------遍历数据到实体集合开始-----------------------------------
        List<String> bardlist = new ArrayList<>();//存入错误提示的list
        List<T> listBean = new ArrayList<T>();
        Field[] fields = tClass.getDeclaredFields();
        List<Field> fields2 = new ArrayList<>();
        for (Field field : fields) {
            if (field.getAnnotation(ExcelPropertyCheck.class) != null) {
                fields2.add(field);
            }
        }
        fields2.sort((o1, o2) -> {
            Integer index1 = o1.getAnnotation(ExcelPropertyCheck.class).index();
            Integer index2 = o2.getAnnotation(ExcelPropertyCheck.class).index();
            return index1.compareTo(index2);
        });
        fields = fields2.toArray(new Field[fields2.size()]);
        T uBean = null;
        for (int i = 1; i < list.size(); i++) {// i=1表示第一行不要,i=0表示要表头
            uBean = (T) tClass.newInstance();
            List<String> listStr = list.get(i);
            int a = 0;
            for (int j = 0; j < listStr.size(); j++) {
                Field field = fields[j];
                field.setAccessible(true);
                ExcelPropertyCheck annotation = field.getAnnotation(ExcelPropertyCheck.class);
                String datastring = listStr.get(j);
                if (datastring.length() == 0 && annotation.required()) {
                    bardlist.add("第" + (i + 1) + "行列名称为:" + annotation.name() + " 存在空值");
                    a++;
                    continue;
                }
                if (datastring.length() > 0) {
                    if (-1 != annotation.maxLength() && annotation.maxLength() < datastring.length()) {
                        bardlist.add("第" + (i + 1) + "行列名称为:" + annotation.name() + " 字符串超长,规定长度为:" + annotation.maxLength());
                        a++;
                        continue;
                    }
                    Class<?> type = field.getType();
                    // 只支持8中基本类型和String类型 如有其他类型 请自行添加
                    try {
                        if (type == String.class) {
                            field.set(uBean, datastring);
                        } else if (type == Integer.class || type == int.class) {
                            field.set(uBean, Integer.parseInt(datastring));
                        } else if (type == Double.class || type == double.class) {
                            field.set(uBean, Double.parseDouble(datastring));
                        } else if (type == Float.class || type == float.class) {
                            field.set(uBean, Float.parseFloat(datastring));
                        } else if (type == Long.class || type == long.class) {
                            field.set(uBean, Long.parseLong(datastring));
                        } else if (type == Boolean.class || type == boolean.class) {
                            field.set(uBean, Boolean.parseBoolean(datastring));
                        } else if (type == Short.class || type == short.class) {
                            field.set(uBean, Short.parseShort(datastring));
                        } else if (type == Byte.class || type == byte.class) {
                            field.set(uBean, Byte.parseByte(datastring));
                        } else if (type == Character.class || type == char.class) {
                            field.set(uBean, datastring.charAt(0));
                        }
                    } catch (Exception e) {
                        log.info("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:" + type.getName().substring(type.getName().lastIndexOf(".") + 1));
                        bardlist.add("第" + (i + 1) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:" + type.getName().substring(type.getName().lastIndexOf(".") + 1));
                        a++;
                        continue;
                    }
                }
            }
            if (a == 0) {
                listBean.add(uBean);
            }
        }
        StringBuilder stringBuilder = new StringBuilder();
//        stringBuilder.append("成功导入"+listBean.size()+"条数据，"+"失败条数为"+(list.size()-1-listBean.size())+".").append("<br/>");
        if (bardlist.size() != 0) {
            for (String s : bardlist) {
                stringBuilder.append(s).append("<br/>");
            }
        }
        ExcelResult excelResult = new ExcelResult();
        excelResult.setData(listBean);
        excelResult.setMessage(stringBuilder.toString());
        excelResult.setSize(list.size() - 1);
        return excelResult;
    }

    public static <T> ExcelResult readExcel2Bean(InputStream is, Class<T> tClass)
            throws IOException, IllegalAccessException, InstantiationException {

        List<List<String>> list = ExcelReaderUtil.readExcel(is);
        //-----------------------遍历数据到实体集合开始-----------------------------------
        List<String> bardlist = new ArrayList<>();//存入错误提示的list
        List<T> listBean = new ArrayList<T>();
        Field[] fields = tClass.getDeclaredFields();
        List<Field> fields2 = new ArrayList<>();
        for (Field field : fields) {
            if (field.getAnnotation(ExcelPropertyCheck.class) != null) {
                fields2.add(field);
            }
        }
        fields2.sort((o1, o2) -> {
            Integer index1 = o1.getAnnotation(ExcelPropertyCheck.class).index();
            Integer index2 = o2.getAnnotation(ExcelPropertyCheck.class).index();
            return index1.compareTo(index2);
        });
        fields = fields2.toArray(new Field[fields2.size()]);
        T uBean = null;
        for (int i = 0; i < list.size(); i++) {// i=1表示第一行不要,i=0表示要表头
            uBean = (T) tClass.newInstance();
            List<String> listStr = list.get(i);
            int a = 0;
            for (int j = 0; j < listStr.size(); j++) {
                Field field = fields[j];
                field.setAccessible(true);
                ExcelPropertyCheck annotation = field.getAnnotation(ExcelPropertyCheck.class);
                String datastring = listStr.get(j);
                if (datastring.length() == 0 && annotation.required()) {
                    bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 为必填项,不可为空");
                    a++;
                    continue;
                }
                if (datastring.length() > 0) {
                    if (-1 != annotation.maxLength() && annotation.maxLength() < datastring.length()) {
                        bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 字符串超长,规定长度为:" + annotation.maxLength());
                        a++;
                        continue;
                    }
                    Class<?> type = field.getType();
                    // 只支持8中基本类型和String类型 如有其他类型 请自行添加

                    if (type == String.class) {
                        field.set(uBean, datastring);
                    } else if (type == BigDecimal.class) {
                        boolean number = NumberUtils.isNumber(datastring);
                        if (number) {
                            if (-1 != annotation.pointmaxLength()) {
                                //datastring获取他的小数部分位数
                                if (datastring.contains(".")) {
                                    String substring = datastring.substring(datastring.indexOf(".") + 1);
                                    if (substring.length() > annotation.pointmaxLength()) {
                                        bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 未保留" + annotation.pointmaxLength() + "位小数");
                                        a++;
                                        continue;
                                    }
                                }
                            }
                            field.set(uBean, new BigDecimal(datastring));
                        } else {
                            log.info("第" + (i + 3) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:小数");
                            bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:小数");
                            a++;
                            continue;
                        }
                    }

                }
            }
            if (listStr.get(7).length() > 0) {
                List<String> strings = Arrays.asList(listStr.get(7), listStr.get(8), listStr.get(9));
                BigDecimal checkResult = check(strings);
                int compareTo = checkResult.compareTo(new BigDecimal(100));
                if (compareTo != 0) {
                    bardlist.add("第" + (i + 3) + "行人口比例的和不为100%");
                    a++;
                    continue;
                }
            }
            List<String> strings = Arrays.asList(listStr.get(12), listStr.get(13), listStr.get(14), listStr.get(15), listStr.get(16), listStr.get(17), listStr.get(18), listStr.get(19), listStr.get(20));
            BigDecimal checkResult = check(strings);
            int compareTo = checkResult.compareTo(new BigDecimal(100));
            if (compareTo != 0) {
                bardlist.add("第" + (i + 3) + "行城镇地区结构类型占比的和不为100%");
                a++;
                continue;
            }
            if (listStr.get(21).length() > 0) {
                List<String> strings2 = Arrays.asList(listStr.get(21), listStr.get(22), listStr.get(23), listStr.get(24), listStr.get(25), listStr.get(26), listStr.get(27), listStr.get(28));
                BigDecimal checkResult2 = check(strings2);
                int compareTo2 = checkResult2.compareTo(new BigDecimal(100));
                if (compareTo2 != 0) {
                    bardlist.add("第" + (i + 3) + "行城镇地区年代类型占比的和不为100%");
                    a++;
                    continue;
                }
            }
            List<String> strings3 = Arrays.asList(listStr.get(30), listStr.get(31), listStr.get(32), listStr.get(33), listStr.get(34), listStr.get(35), listStr.get(36), listStr.get(37), listStr.get(38));
            BigDecimal checkResult3 = check(strings3);
            int compareTo3 = checkResult3.compareTo(new BigDecimal(100));
            if (compareTo3 != 0) {
                bardlist.add("第" + (i + 3) + "行农村地区结构类型占比的和不为100%");
                a++;
                continue;
            }
            if (listStr.get(39).length() > 0) {
                List<String> strings4 = Arrays.asList(listStr.get(39), listStr.get(40), listStr.get(41), listStr.get(42), listStr.get(43), listStr.get(44), listStr.get(45));
                BigDecimal checkResult4 = check(strings4);
                int compareTo4 = checkResult4.compareTo(new BigDecimal(100));
                if (compareTo4 != 0) {
                    bardlist.add("第" + (i + 3) + "行农村地区年代类型占比的和不为100%");
                    a++;
                    continue;
                }
            }
            if (a == 0) {
                listBean.add(uBean);
            }
        }
        StringBuilder stringBuilder = new StringBuilder();
//        stringBuilder.append("成功导入"+listBean.size()+"条数据，"+"失败条数为"+(list.size()-1-listBean.size())+".").append("<br/>");
        if (bardlist.size() != 0) {
            for (String s : bardlist) {
                stringBuilder.append(s).append("<br/>");
            }
        }
        ExcelResult excelResult = new ExcelResult();
        excelResult.setData(listBean);
        excelResult.setMessage(stringBuilder.toString());
        excelResult.setSize(list.size());
        return excelResult;
    }

    private static BigDecimal check(List<String> strings) {
        BigDecimal zero = BigDecimal.ZERO;
        for (String string : strings) {
            if (PlatformObjectUtils.isNotEmpty(string)) {
                zero = zero.add(new BigDecimal(string));
            }

        }
        return zero;
    }

    public static <T> ExcelResult readExcel3Bean(InputStream is, Class<T> tClass)
            throws IOException, IllegalAccessException, InstantiationException {

        List<List<String>> list = ExcelReaderUtil.readExcel(is);
        //-----------------------遍历数据到实体集合开始-----------------------------------
        List<String> bardlist = new ArrayList<>();//存入错误提示的list
        List<T> listBean = new ArrayList<T>();
        Field[] fields = tClass.getDeclaredFields();
        List<Field> fields2 = new ArrayList<>();
        for (Field field : fields) {
            if (field.getAnnotation(ExcelPropertyCheck.class) != null) {
                fields2.add(field);
            }
        }
        fields2.sort((o1, o2) -> {
            Integer index1 = o1.getAnnotation(ExcelPropertyCheck.class).index();
            Integer index2 = o2.getAnnotation(ExcelPropertyCheck.class).index();
            return index1.compareTo(index2);
        });
        fields = fields2.toArray(new Field[fields2.size()]);
        T uBean = null;
        for (int i = 0; i < list.size(); i++) {// i=1表示第一行不要,i=0表示要表头
            uBean = (T) tClass.newInstance();
            List<String> listStr = list.get(i);
            int a = 0;
            for (int j = 0; j < listStr.size(); j++) {
                Field field = fields[j];
                field.setAccessible(true);
                ExcelPropertyCheck annotation = field.getAnnotation(ExcelPropertyCheck.class);
                String datastring = listStr.get(j);
                if (datastring.length() == 0 && annotation.required()) {
                    bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 为必填项,不可为空");
                    a++;
                    continue;
                }
                if (datastring.length() > 0) {
                    if (-1 != annotation.maxLength() && annotation.maxLength() < datastring.length()) {
                        bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 字符串超长,规定长度为:" + annotation.maxLength());
                        a++;
                        continue;
                    }
                    Class<?> type = field.getType();
                    // 只支持8中基本类型和String类型 如有其他类型 请自行添加

                    if (type == String.class) {
                        field.set(uBean, datastring);
                    } else if (type == BigDecimal.class) {
                        boolean number = NumberUtils.isNumber(datastring);
                        if (number) {
                            if (-1 != annotation.pointmaxLength()) {
                                //datastring获取他的小数部分位数
                                if (datastring.contains(".")) {
                                    String substring = datastring.substring(datastring.indexOf(".") + 1);
                                    if (substring.length() > annotation.pointmaxLength()) {
                                        bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 未保留" + annotation.pointmaxLength() + "位小数");
                                        a++;
                                        continue;
                                    }
                                }
                            }
                            field.set(uBean, new BigDecimal(datastring));
                        } else {
                            log.info("第" + (i + 3) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:小数");
                            bardlist.add("第" + (i + 3) + "行列名称为:" + annotation.name() + " 类型错误,规定类型为:小数");
                            a++;
                            continue;
                        }
                    }

                }
            }
            if (listStr.get(8).length() > 0) {
                List<String> strings = Arrays.asList(listStr.get(10), listStr.get(8), listStr.get(9));
                BigDecimal checkResult = check(strings);
                int compareTo = checkResult.compareTo(new BigDecimal(100));
                if (compareTo != 0) {
                    bardlist.add("第" + (i + 3) + "行人口比例的和不为100%");
                    a++;
                    continue;
                }
            }
            List<String> strings = Arrays.asList(listStr.get(21), listStr.get(13), listStr.get(14), listStr.get(15), listStr.get(16), listStr.get(17), listStr.get(18), listStr.get(19), listStr.get(20));
            BigDecimal checkResult = check(strings);
            int compareTo = checkResult.compareTo(new BigDecimal(100));
            if (compareTo != 0) {
                bardlist.add("第" + (i + 3) + "行城镇地区结构类型占比的和不为100%");
                a++;
                continue;
            }
            if (listStr.get(22).length() > 0) {
                List<String> strings2 = Arrays.asList(listStr.get(27), listStr.get(22), listStr.get(23), listStr.get(24), listStr.get(25), listStr.get(26), listStr.get(28), listStr.get(29));
                BigDecimal checkResult2 = check(strings2);
                int compareTo2 = checkResult2.compareTo(new BigDecimal(100));
                if (compareTo2 != 0) {
                    bardlist.add("第" + (i + 3) + "行城镇地区年代类型占比的和不为100%");
                    a++;
                    continue;
                }
            }
            List<String> strings3 = Arrays.asList(listStr.get(39), listStr.get(31), listStr.get(32), listStr.get(33), listStr.get(34), listStr.get(35), listStr.get(36), listStr.get(37), listStr.get(38));
            BigDecimal checkResult3 = check(strings3);
            int compareTo3 = checkResult3.compareTo(new BigDecimal(100));
            if (compareTo3 != 0) {
                bardlist.add("第" + (i + 3) + "行农村地区结构类型占比的和不为100%");
                a++;
                continue;
            }
            if (listStr.get(40).length() > 0) {
                List<String> strings4 = Arrays.asList(listStr.get(46),listStr.get(45), listStr.get(40), listStr.get(41), listStr.get(42), listStr.get(43), listStr.get(44));
                BigDecimal checkResult4 = check(strings4);
                int compareTo4 = checkResult4.compareTo(new BigDecimal(100));
                if (compareTo4 != 0) {
                    bardlist.add("第" + (i + 3) + "行农村地区年代类型占比的和不为100%");
                    a++;
                    continue;
                }
            }
            if (a == 0) {
                listBean.add(uBean);
            }
        }
        StringBuilder stringBuilder = new StringBuilder();
//        stringBuilder.append("成功导入"+listBean.size()+"条数据，"+"失败条数为"+(list.size()-1-listBean.size())+".").append("<br/>");
        if (bardlist.size() != 0) {
            for (String s : bardlist) {
                stringBuilder.append(s).append("<br/>");
            }
        }
        ExcelResult excelResult = new ExcelResult();
        excelResult.setData(listBean);
        excelResult.setMessage(stringBuilder.toString());
        excelResult.setSize(list.size());
        return excelResult;
    }
}


